from flask import Flask, request, jsonify
from flask_cors import CORS
import mysql.connector
import os
import uuid
from datetime import datetime, timedelta
from config import settings
from qiniu_util import upload_to_qiniu
from ocr import recognize_invoice

app = Flask(__name__)
CORS(app, supports_credentials=True)

# 根路径路由
@app.route('/')
def home():
    return "发票管理系统后端服务已启动！"

# 数据库连接
def get_db():
    return  mysql.connector.connect(**settings.MYSQL_CONFIG)


# 创建数据表
def init_db():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS invoices (
            id VARCHAR(36) PRIMARY KEY,
            image_url VARCHAR(255) NOT NULL,
            invoice_code VARCHAR(20),
            invoice_number VARCHAR(20),
            date DATE,
            amount DECIMAL(10,2),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()

import tempfile
import os

@app.route('/upload', methods=['POST'])
def upload_invoice():
    if 'file' not in request.files:
        return jsonify({"error": "No file uploaded"}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({"error": "No selected file"}), 400
    
    # 打印上传的文件信息
    print("上传的文件名:", file.filename)
    print("上传的文件 MIME 类型:", file.mimetype)

    # 生成临时文件路径
    file_ext = file.filename.split('.')[-1]
    #temp_file = tempfile.NamedTemporaryFile(delete=False, suffix=f".{file_ext}")
    with tempfile.NamedTemporaryFile(delete=False, suffix=f".{file_ext}") as temp_file:
        temp_path = temp_file.name
        file.save(temp_path)
        #temp_file.close()  # 关闭文件

    # 打印临时文件路径（调试用）
    print("临时文件路径:", temp_path)

    # 上传到七牛云
    file_name = os.path.basename(temp_path)
    image_url = upload_to_qiniu(temp_path, file_name)
    print("生成的图片 URL:", image_url)  # 打印图片 URL
    #os.remove(temp_path)  # 删除临时文件

    if not image_url:
        return jsonify({"error": "Upload failed"}), 500

    # OCR识别
    ocr_data = recognize_invoice(image_url)
    if 'error_code' in ocr_data:
        return jsonify({"error": "OCR recognition failed"}), 500

    from datetime import datetime

# 解析OCR结果
    try:
        inner_dict = ocr_data.get('words_result', {})
        invoice_date = inner_dict.get('InvoiceDate', '1970年01月01日')
        print(invoice_date)
        #if '年' in invoice_date and '月' in invoice_date and '日' in invoice_date:
            #date_obj = datetime.strptime(invoice_date, '%Y年%m月%d日').date()
        #else:
            #date_obj = datetime.strptime('1970年01月01日', '%Y年%m月%d日').date()  # 使用默认值

        # 解析OCR结果
        invoice_info = {
            'id': str(uuid.uuid4()),
            'image_url': image_url,
            'invoice_code': inner_dict.get('InvoiceCode'),
            'invoice_number': inner_dict.get('InvoiceNum'),
            #'invoice_code': ocr_data.get('InvoiceCode', ''),
            #'invoice_number': ocr_data.get('InvoiceNumber', ''),
            #'date': datetime.strptime(ocr_data['InvoiceDate'], '%Y年%m月%d日').date(),
            #'amount': float(ocr_data['TotalAmount'])
            'date': datetime.strptime(inner_dict.get('InvoiceDate', '1970年01月01日'), '%Y年%m月%d日').date(),  # 使用默认值
            'amount': float(inner_dict.get('TotalAmount','10'))
        }

    except Exception as e:
        print("日期解析失败:", e)
        return jsonify({"error": "日期解析失败"}), 500

    # 存储到数据库
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO invoices 
        (id, image_url, invoice_code, invoice_number, date, amount)
        VALUES (%s, %s, %s, %s, %s, %s)
    """, tuple(invoice_info.values()))
    conn.commit()
    conn.close()

    return jsonify({"message": "Upload success", "data": invoice_info}), 200
    response_data = {
    "message": "Upload success",
    "data": {
        "image_url": image_url,
        "invoice_code": "123456",
        "invoice_number": "789012",
        "date": "2023-10-01",
        "amount": 100.0
    }
    }
    print("返回的响应数据:", response_data)
    return jsonify(response_data), 200

@app.route('/query', methods=['GET'])
def query_invoices():
    try:
        # 解析查询参数
        start_date = request.args.get('start')
        end_date = request.args.get('end')
        keyword = request.args.get('keyword')
        query = request.args.get('query')##

        conn = get_db()
        cursor = conn.cursor(dictionary=True)

        query = "SELECT * FROM invoices WHERE 1=1"
        params = []

        if start_date and end_date:
            print(f"开始日期: {start_date}, 结束日期: {end_date}")  # 添加日志输出
            query += " AND date BETWEEN %s AND %s"
            params.extend([start_date, end_date])
        if keyword:
            query += " AND (invoice_code LIKE %s OR invoice_number LIKE %s)"
            params.extend([f"%{keyword}%", f"%{keyword}%"])
        print("执行的 SQL 语句:", query, "参数:", params)
        cursor.execute(query, params)
        results = cursor.fetchall()
        conn.close()

        # 转换日期格式
        for item in results:
            item['date'] = item['date'].isoformat()
            item['amount'] = float(item['amount'])  # 将 amount 转换为浮点数
        return jsonify(results)
    except Exception as e:
        print(f"查询发票数据时出错: {e}")
        return jsonify({"error": "查询发票数据时出错"}), 500  

@app.route('/stats', methods=['GET'])
def get_stats():
    start_date = request.args.get('start')
    end_date = request.args.get('end')
    keyword = request.args.get('keyword')
    query = request.args.get('query')##
    if query:
        start_date, end_date, keyword = parse_natural_query(query)
    # 参数解析同query
    conn = get_db()
    cursor = conn.cursor()

    query = "SELECT COUNT(*) as total_count, SUM(amount) as total_amount FROM invoices WHERE 1=1"
    params = []
    # 添加过滤条件（同query）
    # ...
    if start_date and end_date:
        print(f"开始日期: {start_date}, 结束日期: {end_date}")  # 添加日志输出
        query += " AND date BETWEEN %s AND %s"
        params.extend([start_date, end_date])
    if keyword:
        query += " AND (invoice_code LIKE %s OR invoice_number LIKE %s)"
        params.extend([f"%{keyword}%", f"%{keyword}%"])
    print("执行的 SQL 语句:", query, "参数:", params)  # 添加日志输出
    
    try:
        cursor.execute(query, params)
        result = cursor.fetchone()
        conn.close()

        total_count = result[0] if result[0] else 0
        total_amount = float(result[1]) if result[1] else 0

        return jsonify({
            "total_count": total_count,
            "total_amount": total_amount
        })
    except mysql.connector.Error as err:
        print(f"统计发票数据时出错: {err}")
        return jsonify({"error": "统计发票数据时出错"}), 500

import re
import jieba

@app.route('/natural_query', methods=['GET'])
def natural_query():
    query = request.args.get('query')
    start_date = None
    end_date = None
    keyword = None

    # 改进日期匹配逻辑，增加对年-月格式的匹配
    # 日期匹配
    date_range_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日至(\d{4})年(\d{1,2})月(\d{1,2})日', query)
    single_date_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日', query)
    if date_range_match:
        start_year = date_range_match.group(1)
        start_month = date_range_match.group(2).zfill(2)
        start_day = date_range_match.group(3).zfill(2)
        end_year = date_range_match.group(4)
        end_month = date_range_match.group(5).zfill(2)
        end_day = date_range_match.group(6).zfill(2)
        start_date = f"{start_year}-{start_month}-{start_day}"
        end_date = f"{end_year}-{end_month}-{end_day}"
    elif single_date_match:
        year = single_date_match.group(1)
        month = single_date_match.group(2).zfill(2)
        day = single_date_match.group(3).zfill(2)
        start_date = f"{year}-{month}-{day}"
        end_date = start_date
    print('开始日期和结束日期',start_date,end_date)
    # 改进关键字匹配逻辑，只要不是日期相关的词，都可能作为关键字
    common_query_words = ["查询","日期", "查找", "获取", "统计", "的", "日至", "金额","发票", "发票代码", "为","代码"]
    date_pattern = re.compile(r'\d{4}年\d{1,2}月\d{1,2}日')
    amount_match = re.search(r'金额为[^\d]*([\d.]+)', query)
    if amount_match:
        keyword = amount_match.group(1)
    else:
        words = jieba.lcut(query)
        date_numbers = []
        if date_range_match:
            date_numbers.extend([date_range_match.group(1), date_range_match.group(2), date_range_match.group(3),
                                 date_range_match.group(4), date_range_match.group(5), date_range_match.group(6)])
        elif single_date_match:
            date_numbers.extend([single_date_match.group(1), single_date_match.group(2), single_date_match.group(3)])
        for word in words:
            if word not in common_query_words and word not in date_numbers and not date_pattern.search(word) and not re.match(r'\d{4}', word) and len(word) > 1:
                keyword = word
                break

    conn = get_db()
    cursor = conn.cursor(dictionary=True)

    sql_query = "SELECT * FROM invoices WHERE 1=1"
    params = []

    if start_date and end_date:
        sql_query += " AND date BETWEEN %s AND %s"
        params.extend([start_date, end_date])
    if keyword:
        sql_query += " AND (invoice_code LIKE %s OR invoice_number LIKE %s OR amount LIKE %s)"
        params.extend([f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"])

    if not keyword:
        sql_query = sql_query.replace(" AND (invoice_code LIKE %s OR invoice_number LIKE %s OR amount LIKE %s)", "")
        params = params[:2]

    print("执行的 SQL 语句:", sql_query, "参数:", params)
    try:
        cursor.execute(sql_query, params)
        results = cursor.fetchall()
        print(f"查询到的结果数量: {len(results)}")
        if len(results) == 0:
            print("未查询到符合条件的记录，可能是关键字或日期匹配问题。")
    except mysql.connector.Error as err:
        print(f"数据库查询出错: {err}")
        return jsonify({"error": "数据库查询出错"}), 500
    finally:
        conn.close()

    for item in results:
        item['date'] = item['date'].isoformat()
    return jsonify(results)

def parse_natural_query(query):
    start_date = None
    end_date = None
    keyword = None

    # 日期匹配
    date_range_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日至(\d{4})年(\d{1,2})月(\d{1,2})日', query)
    single_date_match = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日', query)
    if date_range_match:
        start_year = date_range_match.group(1)
        start_month = date_range_match.group(2).zfill(2)
        start_day = date_range_match.group(3).zfill(2)
        end_year = date_range_match.group(4)
        end_month = date_range_match.group(5).zfill(2)
        end_day = date_range_match.group(6).zfill(2)
        start_date = f"{start_year}-{start_month}-{start_day}"
        end_date = f"{end_year}-{end_month}-{end_day}"
    elif single_date_match:
        year = single_date_match.group(1)
        month = single_date_match.group(2).zfill(2)
        day = single_date_match.group(3).zfill(2)
        start_date = f"{year}-{month}-{day}"
        end_date = start_date

    # 改进关键字匹配逻辑，只要不是日期相关的词，都可能作为关键字
    common_query_words = ["查询","日期", "查找", "获取", "统计", "的", "日至", "金额","发票", "发票代码", "为","代码"]
    date_pattern = re.compile(r'\d{4}年\d{1,2}月\d{1,2}日')
    amount_match = re.search(r'金额为[^\d]*([\d.]+)', query)
    if amount_match:
        keyword = amount_match.group(1)
    else:
        words = jieba.lcut(query)
        date_numbers = []
        if date_range_match:
            date_numbers.extend([date_range_match.group(1), date_range_match.group(2), date_range_match.group(3),
                                 date_range_match.group(4), date_range_match.group(5), date_range_match.group(6)])
        elif single_date_match:
            date_numbers.extend([single_date_match.group(1), single_date_match.group(2), single_date_match.group(3)])
        for word in words:
            if word not in common_query_words and word not in date_numbers and not date_pattern.search(word) and not re.match(r'\d{4}', word) and len(word) > 1:
                keyword = word
                break

    return start_date, end_date, keyword

if __name__ == '__main__':
    init_db()
    app.run(host='0.0.0.0', port=5000, debug=True)